# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
from ebook.dbHelp import DBHelp
from ebook.items import EbookItem
from ebook.items import EbookTypeItem


class EbookPipeline:
    def __init__(self):
        self.dbHelp = DBHelp()
        self.dbHelp.open('localhost', 'root', '123456', 'novel', 'utf8')

    def process_item(self, item, spider):
        if isinstance(item, EbookTypeItem):
            verify_sql = 'select * from ebook_type where type_title = "{}"'.format(item['type_title'])
            row = self.dbHelp.select(verify_sql)[0]
            if row == 0:
                sql = 'insert into ebook_type(type_title) values("{}")'.format(item['type_title'])
                self.dbHelp.insert(sql)
            print(item)
        elif isinstance(item, EbookItem):
            print(item)
            verify_sql = 'select * from ebook where ebook_title = "{}"'.format(item['ebook_title'])
            row = self.dbHelp.select(verify_sql)[0]
            if row == 0:
                get_type_id_sql = 'select type_id from ebook_type where type_title = "{}"'.format(item['type_title'])
                type_id = self.dbHelp.select(get_type_id_sql)[1]
                sql = 'insert into ebook(ebook_title, ebook_author, ebook_cover, ebook_tag,' \
                            'ebook_ctime, ebook_ISBN, ebook_eintro, ebook_aintro, type_id) ' \
                      'values("{}","{}","{}","{}","{}","{}","{}","{}",{})'\
                    .format(item['ebook_title'], item['ebook_author'], item['ebook_cover'], item["ebook_tag"],
                            item['ebook_ctime'], item['ebook_ISBN'], item['ebook_eintro'], item['ebook_aintro'], type_id[0])
                self.dbHelp.insert(sql)

    def close_spider(self, spider):
        self.dbHelp.close()
